import json
import xlsxwriter
from io import BytesIO
from apis import api_routes
from flask import g, request, jsonify, Response
from security.decoration import certification
from models.logs.logs import Logs
from models.logs.gather import Gather
from models.facility.facility import Facility
from public.local import date_type
from datetime import datetime, timedelta
from sqlalchemy import and_
from typing import List, Union
from urllib.parse import unquote, quote
from public.local import local_time


@api_routes.route("/gathers/kind", methods=["GET"])
def func_gathers_kind_get_api():
    """
    查询采集数据类型名称, 完成API开发任务
    http://127.0.0.1:8080/api/gathers/kind
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    }
    :return:
        {'status': 200, 'data': resp, "index": index, "size": size, "total": count, "pages": page}
    ---
    tags:
      - 采集数据模块
    """
    resp = [
        "入下胶盖压力数据",
        "入下胶盖压位移数据",
        "入上胶盖压力数据",
        "入上胶盖压位移数据",
        "端子入上胶盖位移数据",
        "二次压合位移数据",

        "第一个端子压力",
        "第一个端子位移",
        "第二个端子压力",
        "第二个端子位移",
        "第三个端子压力",
        "第三个端子位移",
    ]
    return jsonify({'status': 200, 'data': resp})


@api_routes.route("/gathers", methods=["GET"])
def func_gathers_get_api():
    """
    查询采集信息, 只要有token 都可以访问, 完成API开发任务
    http://127.0.0.1:8080/api/gathers
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    "start"        string          开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"       string          结束时间，默认今天时间  格式 %Y-%m-%d
    "facility_id"  int             设备ID
    "name"         string          查询种类
    "sequence"     string          产品SN
    }
    :return:
        {'status': 200, 'data': resp, "index": index, "size": size, "total": count, "pages": page}
    ---
    tags:
      - 采集数据模块
    """
    size = request.args.get("size", default=10, type=int)
    index = request.args.get("index", default=1, type=int)
    start = request.args.get("start", default=None, type=str)
    finish = request.args.get("finish", default=None, type=str)
    kings: Union[str, None] = request.args.get("name", default=None, type=str)
    ft_id: Union[int, None] = request.args.get("facility_id", default=None, type=int)
    sequence: Union[str, None] = request.args.get("sequence", default=None, type=str)

    arg0, arg1 = start, finish
    if start is None or len(start) == 0:
        # arg0 = date_type()
        arg0 = None
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")
    if finish is None or len(finish) == 0:
        arg1 = None
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    parameter: list = []
    if arg0 is not None:
        parameter.append(Gather.time >= arg0)
    if arg1 is not None:
        parameter.append(Gather.time <= arg1)
    if kings is not None and len(kings) >= 1:
        parameter.append(Gather.name == kings)
    if ft_id is not None:
        parameter.append(Gather.ft_id == ft_id)

    if sequence is not None and len(sequence) >= 1:
        parameter.append(Gather.sequence == sequence)

    count = g.session.query(Gather.id).filter(and_(* parameter)).count()
    page: int = int((count / size) + 1) if (count / size) > int(count / size) else int(count / size)

    ftall = {}
    fts: List[Facility] = g.session.query(Facility).order_by(Facility.id).all()
    for xx in fts:
        ftall[xx.id] = xx.name

    data: List[Gather] = g.session.query(Gather).filter(and_(* parameter)).order_by(Gather.id).offset((index - 1) * size).limit(size).all()
    resp: list = []
    for index, itr in enumerate(data):
        temp_data: dict = json.loads(itr.message)
        max_data = round(max(temp_data.get("data")), 4)
        min_data = round(min(temp_data.get("data")), 4)
        average_data = round(sum(temp_data.get("data")) / len(temp_data.get("data")), 4)

        temp_data["max_data"] = max_data
        temp_data["min_data"] = min_data
        temp_data["average_data"] = average_data

        resp.append({
            "gather_id": itr.id,
            "time": itr.time.strftime("%Y-%m-%d %H:%M:%S"),
            "sequence": itr.sequence,
            "status": itr.status,
            "facility_id": itr.ft_id,
            "facility_name": ftall[itr.ft_id],
            "name": itr.name,
            "message": temp_data
        })

    doc: dict = {
        "gather_id": "数据库ID",
        "time": "操作时间",
        "sequence": "产品SN",
        "status": "是否合格",
        "facility_id": "设备ID",
        "facility_name": "设备名称",
        "name": "采集数据类型",
        "message": "数据"
    }
    return jsonify({'status': 200, 'data': resp, "index": index, "size": size, "total": count, "pages": page, "doc": doc})


@api_routes.route("/gathers/output", methods=["GET"])
def func_gathers_output_get_api():
    """
    查询采集信息导出EXCEL, 只要有token 都可以访问, 完成API开发任务
    http://127.0.0.1:8080/api/gathers/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"        string          开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"       string          结束时间，默认今天时间  格式 %Y-%m-%d
    "facility_id"  int             设备ID
    "name"         string          查询种类
    "sequence"     string          产品SN
    }
    :return:
        {'status': 200, 'data': resp, "index": index, "size": size, "total": count, "pages": page}
    ---
    tags:
      - 采集数据模块
    """
    start = request.args.get("start", default=None, type=str)
    finish = request.args.get("finish", default=None, type=str)
    kings: Union[str, None] = request.args.get("name", default=None, type=str)
    ft_id: Union[int, None] = request.args.get("facility_id", default=None, type=int)
    sequence: Union[str, None] = request.args.get("sequence", default=None, type=str)

    arg0, arg1 = start, finish
    if start is None or len(start) == 0:
        # arg0 = date_type()
        arg0 = None
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")
    if finish is None or len(finish) == 0:
        arg1 = None
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    parameter: list = []
    if arg0 is not None:
        parameter.append(Gather.time >= arg0)
    if arg1 is not None:
        parameter.append(Gather.time <= arg1)
    if kings is not None and len(kings) >= 1:
        parameter.append(Gather.name == kings)
    if ft_id is not None:
        parameter.append(Gather.ft_id == ft_id)

    if sequence is not None and len(sequence) >= 1:
        parameter.append(Gather.sequence == sequence)

    ftall = {}
    fts: List[Facility] = g.session.query(Facility).order_by(Facility.id).all()
    for xx in fts:
        ftall[xx.id] = xx.name

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("系统日志历史数据表")
    worksheet_index.set_column("A:ZZ", 28)
    message: list = ["设备名称", "采集名称", "产品SN码", "加工时间", "是否合格", "测试下限", "最小值", "平均值", "最大值", "测试上限"]
    worksheet_index.write_row(row=0, col=0, data=message)

    db_data: List[Gather] = g.session.query(Gather).filter(and_(*parameter)).order_by(Gather.id).all()
    for index, itr in enumerate(db_data):
        temp_data: dict = json.loads(itr.message)
        temp = [
            ftall[itr.ft_id],
            itr.name,
            itr.sequence,
            itr.time.strftime("%Y-%m-%d %H:%M:%S"),
            "合格" if itr.status else "不合格",
            round(temp_data.get("floor"), 4),
            round(min(temp_data.get("data")), 4),
            round(sum(temp_data.get("data")) / len(temp_data.get("data")), 4),
            round(max(temp_data.get("data")), 4),
            round(temp_data.get("upper")),
        ]
        worksheet_index.write_row(row=index + 1, col=0, data=temp)

    file_name = "采集过程数据{}.xlsx".format(local_time())
    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200
